package zy.dao.vip.report.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.vip.report.VipReportDAO;
import zy.entity.vip.member.T_Vip_ActivevipAnalysis;
import zy.entity.vip.member.T_Vip_BrandAnalysis;
import zy.entity.vip.member.T_Vip_ConsumeDetailList;
import zy.entity.vip.member.T_Vip_ConsumeMonthCompare;
import zy.entity.vip.member.T_Vip_Member;
import zy.entity.vip.member.T_Vip_NoShopAnalysis;
import zy.entity.vip.member.T_Vip_QuotaAnalysis;
import zy.entity.vip.member.T_Vip_TypeAnalysis;
import zy.entity.vip.set.T_Vip_AgeGroupSetUp;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class VipReportDAOImpl extends BaseDaoImpl implements VipReportDAO{

	@Override
	public Integer point_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_mobile = params.get("vm_mobile");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_vip_pointlist t");
		sql.append(" JOIN t_vip_member m");
		sql.append(" ON m.vm_code=vpl_vm_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" JOIN common_dict d");
		sql.append(" ON t.vpl_type=d.dt_code");
		sql.append(" AND d.dt_type='VIP_POINT'");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND m.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND vm_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND vm_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(vm_shop_code)) {
			sql.append(" AND vpl_shop_name=:vpl_shop_name ");
		}
		if (StringUtil.isNotEmpty(vm_cardcode)) {
			sql.append(" AND INSTR(vm_cardcode, :vm_cardcode) > 0 ");
		}
		if (StringUtil.isNotEmpty(vm_mobile)) {
			sql.append(" AND INSTR(vm_mobile, :vm_mobile) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Member> point_list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_mobile = params.get("vm_mobile");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_cardcode,vm_name,vm_mobile,vpl_date vm_date,");
		sql.append(" vpl_shop_name shop_name,vpl_manager vm_manager,dt_name vm_type,");
		sql.append(" vpl_remark vm_remark,vpl_point vm_points");
		sql.append(" FROM t_vip_pointlist t");
		sql.append(" JOIN t_vip_member m");
		sql.append(" ON m.vm_code=vpl_vm_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" JOIN common_dict d");
		sql.append(" ON t.vpl_type=d.dt_code");
		sql.append(" AND d.dt_type='VIP_POINT'");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND m.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND vm_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND vm_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(vm_shop_code)) {
			sql.append(" AND vpl_shop_name=:vpl_shop_name ");
		}
		if (StringUtil.isNotEmpty(vm_cardcode)) {
			sql.append(" AND INSTR(vm_cardcode, :vm_cardcode) > 0 ");
		}
		if (StringUtil.isNotEmpty(vm_mobile)) {
			sql.append(" AND INSTR(vm_mobile, :vm_mobile) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY vpl_id");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
					new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}

	@Override
	public List<T_Vip_TypeAnalysis> type_analysis_list(
			Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object vm_shop_code = params.get("vm_shop_code");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT mt_code as id,mt_code,mt_name,vip_count,COUNT(DISTINCT shl_vip_code) AS consumer_count,");
		sql.append("SUM(shl_money) AS rebate_monery_sum,SUM(shl_cost_price*shl_amount) AS cost_monery_sum,SUM(shl_money-shl_cost_price*shl_amount) AS profits_sum ");
		sql.append("FROM (SELECT mt_code,mt_name,mt.companyid,COUNT(DISTINCT vm_code) AS vip_count FROM t_vip_member m ");
		sql.append("LEFT JOIN t_vip_membertype mt ON mt.mt_code = m.vm_mt_code AND mt.companyid = m.companyid " );
		sql.append("LEFT JOIN t_base_shop sp ON  sp.sp_code= m.vm_shop_code AND sp.companyid = m.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND vm_shop_code= :vm_shop_code");
		}
		sql.append(" AND m.companyid = :companyid ");
		sql.append("GROUP BY mt_code) t JOIN t_vip_member vm ON t.mt_code=vm.vm_mt_code AND t.companyid = vm.companyid ");
		sql.append(" JOIN t_sell_shoplist shl ON shl.companyid=vm.companyid AND shl.shl_vip_code=vm.vm_code WHERE 1=1   ");
		sql.append("AND (DATE_FORMAT(shl_sysdate,'%Y-%m-%d')>=:begindate AND DATE_FORMAT(shl_sysdate,'%Y-%m-%d')<=:enddate) ");
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND vm_shop_code=:vm_shop_code");
		}
		sql.append(" AND shl.companyid = :companyid ");
		sql.append(" GROUP BY mt_code ");
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
					new BeanPropertyRowMapper<>(T_Vip_TypeAnalysis.class));
	}

	@Override
	public Integer consume_detail_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_mobile = params.get("vm_mobile");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1) ");
		sql.append(" FROM ");
		sql.append(" (SELECT shl.shl_id,shl.shl_number,shl.shl_pd_code,shl.shl_sell_price,shl.shl_price,shl.shl_money,shl.shl_amount,shl.shl_cr_code,");
		sql.append(" shl.shl_sz_code,shl.shl_br_code,shl.shl_vip_code,shl.shl_date,shl.companyid,sp.sp_name,vm.vm_name,vm.vm_cardcode,vm.vm_mobile,vm.vm_manager_code ");
		sql.append(" FROM t_sell_shoplist shl USE INDEX(IDX_SHL_CO_SP_DA)");
		sql.append(" JOIN t_vip_member vm ");
		sql.append(" ON vm.vm_code=shl.shl_vip_code AND vm.companyid=shl.companyid");
		sql.append(" JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=shl.shl_shop_code AND sp.companyid=shl.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(vm_cardcode != null && !vm_cardcode.equals("")){
			sql.append(" and INSTR(vm.vm_cardcode ,:vm_cardcode)>0");
		}else{
			sql.append(" AND vm.vm_cardcode<>''");
		}
		if(vm_mobile != null && !vm_mobile.equals("")){
			sql.append(" AND vm_mobile=:vm_mobile");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND shl_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND shl_date <= :enddate ");
		}
		sql.append(" AND vm_state=0");
		sql.append(" AND shl.companyid = :companyid");
		sql.append(" ) t");
		sql.append(" JOIN t_base_product pd ");
		sql.append(" ON pd.pd_code=t.shl_pd_code AND pd.companyid=t.companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public Map<String, Object> consume_detail_sum(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_mobile = params.get("vm_mobile");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" IFNULL(SUM(shl_amount), 0) AS shl_amount,");
		sql.append(" IFNULL(SUM(shl_money), 0) AS shl_money");
		sql.append(" FROM ");
		sql.append(" (SELECT shl.shl_id,shl.shl_number,shl.shl_pd_code,shl.shl_sell_price,shl.shl_price,shl.shl_money,shl.shl_amount,shl.shl_cr_code,");
		sql.append(" shl.shl_sz_code,shl.shl_br_code,shl.shl_vip_code,shl.shl_date,shl.companyid,sp.sp_name,vm.vm_name,vm.vm_cardcode,vm.vm_mobile,vm.vm_manager_code ");
		sql.append(" FROM t_sell_shoplist shl USE INDEX(IDX_SHL_CO_SP_DA)");
		sql.append(" JOIN t_vip_member vm ");
		sql.append(" ON vm.vm_code=shl.shl_vip_code AND vm.companyid=shl.companyid");
		sql.append(" JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=shl.shl_shop_code AND sp.companyid=shl.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(vm_cardcode != null && !vm_cardcode.equals("")){
			sql.append(" and INSTR(vm.vm_cardcode ,:vm_cardcode)>0");
		}else{
			sql.append(" AND vm.vm_cardcode<>''");
		}
		if(vm_mobile != null && !vm_mobile.equals("")){
			sql.append(" AND vm_mobile=:vm_mobile");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND shl_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND shl_date <= :enddate ");
		}
		sql.append(" AND vm_state=0");
		sql.append(" AND shl.companyid = :companyid");
		sql.append(" ) t");
		sql.append(" JOIN t_base_product pd ");
		sql.append(" ON pd.pd_code=t.shl_pd_code AND pd.companyid=t.companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<T_Vip_ConsumeDetailList> consume_detail_list(
			Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_mobile = params.get("vm_mobile");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" shl_id as id,shl_number,pd_no,pd_name,shl_sell_price,shl_price,shl_money,shl_amount,");
		sql.append(" shl_cr_code,shl_sz_code,shl_br_code,shl_date,pd_unit,shl_vip_code,vm_cardcode,vm_mobile,vm_name,vm_manager_code,t.sp_name,");
		sql.append(" (shl_price/shl_sell_price) as shl_discount,");
		sql.append(" (SELECT cr_name FROM t_base_color cr  WHERE cr.cr_code=t.shl_cr_code AND cr.companyid=t.companyid LIMIT 1 ) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz.sz_code=t.shl_sz_code AND sz.companyid=t.companyid LIMIT 1 ) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br.br_code=t.shl_br_code AND br.companyid=t.companyid LIMIT 1 ) AS br_name,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em.em_code = t.vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM ");
		sql.append(" (SELECT shl.shl_id,shl.shl_number,shl.shl_pd_code,shl.shl_sell_price,shl.shl_price,shl.shl_money,shl.shl_amount,shl.shl_cr_code,");
		sql.append(" shl.shl_sz_code,shl.shl_br_code,shl.shl_vip_code,shl.shl_date,shl.companyid,sp.sp_name,vm.vm_name,vm.vm_cardcode,vm.vm_mobile,vm.vm_manager_code ");
		sql.append(" FROM t_sell_shoplist shl USE INDEX(IDX_SHL_CO_SP_DA)");
		sql.append(" JOIN t_vip_member vm ");
		sql.append(" ON vm.vm_code=shl.shl_vip_code AND vm.companyid=shl.companyid");
		sql.append(" JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=shl.shl_shop_code AND sp.companyid=shl.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(vm_cardcode != null && !vm_cardcode.equals("")){
			sql.append(" and INSTR(vm.vm_cardcode ,:vm_cardcode)>0");
		}else{
			sql.append(" AND vm.vm_cardcode<>''");
		}
		if(vm_mobile != null && !vm_mobile.equals("")){
			sql.append(" AND vm_mobile=:vm_mobile");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND shl_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND shl_date <= :enddate ");
		}
		sql.append(" AND vm_state=0");
		sql.append(" AND shl.companyid = :companyid");
		
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY shl_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		sql.append(" ) t");
		sql.append(" JOIN t_base_product pd ");
		sql.append(" ON pd.pd_code=t.shl_pd_code AND pd.companyid=t.companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
					new BeanPropertyRowMapper<>(T_Vip_ConsumeDetailList.class));
	}

	@Override
	public Integer activevip_analysis_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object number = params.get("number");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1)  ");
		sql.append(" FROM ");
		sql.append("(SELECT ");
		sql.append(" vm_id as id,vm_cardcode,vm_name,vm_mobile,vm_sex,vm_total_point,vm_points,");
		sql.append(" (SELECT mt.mt_name FROM t_vip_membertype mt WHERE mt.mt_code=vm.vm_mt_code AND mt.companyid=vm.companyid limit 1) as mt_name,");
		sql.append("  IFNULL(sum(sh_sell_money),0) as buy_money,");
		sql.append(" vm_times,vm_total_money,sh_vip_code,");
		sql.append(" IFNULL(sum(case when sh_state=0 then 1 when sh_state=1 THEN -1  end),0)  buy_count,");
		sql.append(" sh.companyid ");
		sql.append(" FROM t_vip_member vm ");
		sql.append(" LEFT JOIN t_sell_shop sh ");
		sql.append(" ON sh.sh_vip_code = vm.vm_code AND sh.companyid=vm.companyid");
		sql.append(" LEFT JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=sh.sh_shop_code AND sp.companyid=sh.companyid ");
		sql.append(" where 1=1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND sh_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND sh_date <= :enddate ");
		}
		if (CommonUtil.FOUR.equals(shop_type) || CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//加盟店,自营店，合伙店
			sql.append(" AND (vm.vm_shop_code=sh.sh_shop_code  AND vm.vm_shop_code=:shop_code)");
		}else  if (CommonUtil.ONE.equals(shop_type)){//总部
			sql.append(" AND ( ");
			sql.append(" sp_upcode=:shop_code AND sp_shop_type IN('"+CommonUtil.ONE+"','"+CommonUtil.THREE+"','"+CommonUtil.FIVE+"') ");
			if (StringUtil.isNotEmpty(vm_shop_code)){
				sql.append(" AND vm.vm_shop_code=:vm_shop_code");
			}
			sql.append(")");
		}
		sql.append(" AND sh.companyid = :companyid");
		sql.append(" GROUP BY sh_vip_code ");
		if (StringUtil.isNotEmpty(number)){
			sql.append(" limit 0,:number");	
		}
		sql.append(" ) t");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_ActivevipAnalysis> activevip_analysis_list(
			Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object number = params.get("number");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT *,  ");
		sql.append(" (SELECT gd_name from t_vip_grade gd WHERE gd.companyid=t.companyid AND gd.gd_lower <= buy_money AND gd.gd_upper >= buy_money LIMIT 0,1) as gd_name ");
		sql.append(" FROM ");
		
		sql.append("(SELECT ");
		sql.append(" vm_id as id,vm_cardcode,vm_name,vm_mobile,vm_sex,vm_total_point,vm_points,");
		sql.append(" (SELECT mt.mt_name FROM t_vip_membertype mt WHERE mt.mt_code=vm.vm_mt_code AND mt.companyid=vm.companyid limit 1) as mt_name,");
		sql.append("  IFNULL(sum(sh_sell_money),0) as buy_money,");
		sql.append(" vm_times,vm_total_money,sh_vip_code,");
		sql.append(" IFNULL(sum(case when sh_state=0 then 1 when sh_state=1 THEN -1  end),0)  buy_count,");
		sql.append(" sh.companyid ");
		sql.append(" FROM t_vip_member vm ");
		sql.append(" LEFT JOIN t_sell_shop sh ");
		sql.append(" ON sh.sh_vip_code = vm.vm_code AND sh.companyid=vm.companyid");
		sql.append(" LEFT JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=sh.sh_shop_code AND sp.companyid=sh.companyid ");
		sql.append(" where 1=1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND sh_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND sh_date <= :enddate ");
		}
		if (CommonUtil.FOUR.equals(shop_type) || CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//加盟店,自营店，合伙店
			sql.append(" AND (vm.vm_shop_code=sh.sh_shop_code  AND vm.vm_shop_code=:shop_code)");
		}else  if (CommonUtil.ONE.equals(shop_type)){//总部
			sql.append(" AND ( ");
			sql.append(" sp_upcode=:shop_code AND sp_shop_type IN('"+CommonUtil.ONE+"','"+CommonUtil.THREE+"','"+CommonUtil.FIVE+"') ");
			if (StringUtil.isNotEmpty(vm_shop_code)){
				sql.append(" AND vm.vm_shop_code=:vm_shop_code");
			}
			sql.append(")");
		}
		sql.append(" AND sh.companyid = :companyid");
		sql.append(" GROUP BY sh_vip_code ");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY buy_count DESC,buy_money DESC ");
		}
		if (StringUtil.isNotEmpty(number)){
			sql.append(" limit 0,:number");	
		}
		sql.append(" ) t");
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
				new BeanPropertyRowMapper<>(T_Vip_ActivevipAnalysis.class));
	}

	@Override
	public Map<String, Object> activevip_analysis_sum(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object number = params.get("number");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT  ");
		sql.append(" IFNULL(SUM(buy_count), 0) AS buy_count,");
		sql.append(" IFNULL(SUM(buy_money), 0) AS buy_money,");
		sql.append(" IFNULL(SUM(vm_times), 0) AS vm_times,");
		sql.append(" IFNULL(SUM(vm_total_money), 0) AS vm_total_money,");
		sql.append(" IFNULL(SUM(vm_total_point), 0) AS vm_total_point,");
		sql.append(" IFNULL(SUM(vm_points), 0) AS vm_points");
		sql.append(" FROM ");
		
		sql.append("(SELECT ");
		sql.append(" vm_id as id,vm_cardcode,vm_name,vm_mobile,vm_sex,vm_total_point,vm_points,");
		sql.append(" (SELECT mt.mt_name FROM t_vip_membertype mt WHERE mt.mt_code=vm.vm_mt_code AND mt.companyid=vm.companyid limit 1) as mt_name,");
		sql.append("  IFNULL(sum(sh_sell_money),0) as buy_money,");
		sql.append(" vm_times,vm_total_money,sh_vip_code,");
		sql.append(" IFNULL(sum(case when sh_state=0 then 1 when sh_state=1 THEN -1  end),0)  buy_count,");
		sql.append(" sh.companyid ");
		sql.append(" FROM t_vip_member vm ");
		sql.append(" LEFT JOIN t_sell_shop sh ");
		sql.append(" ON sh.sh_vip_code = vm.vm_code AND sh.companyid=vm.companyid");
		sql.append(" LEFT JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=sh.sh_shop_code AND sp.companyid=sh.companyid ");
		sql.append(" where 1=1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND sh_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND sh_date <= :enddate ");
		}
		if (CommonUtil.FOUR.equals(shop_type) || CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//加盟店,自营店，合伙店
			sql.append(" AND (vm.vm_shop_code=sh.sh_shop_code  AND vm.vm_shop_code=:shop_code)");
		}else  if (CommonUtil.ONE.equals(shop_type)){//总部
			sql.append(" AND ( ");
			sql.append(" sp_upcode=:shop_code AND sp_shop_type IN('"+CommonUtil.ONE+"','"+CommonUtil.THREE+"','"+CommonUtil.FIVE+"') ");
			if (StringUtil.isNotEmpty(vm_shop_code)){
				sql.append(" AND vm.vm_shop_code=:vm_shop_code");
			}
			sql.append(")");
		}
		sql.append(" AND sh.companyid = :companyid");
		sql.append(" GROUP BY sh_vip_code ");
		if (StringUtil.isNotEmpty(number)){
			sql.append(" limit 0,:number");	
		}
		sql.append(" ) t");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public Integer noshopvip_analysis_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_manager_code = params.get("vm_manager_code");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) ");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND t.vm_state=0 ");
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND sp.sp_code = :vm_shop_code ");
		}
		if( StringUtil.isNotEmpty(vm_mt_code)){
			sql.append(" AND t.vm_mt_code = :vm_mt_code");
		}
		if( StringUtil.isNotEmpty(vm_manager_code) ){
			sql.append(" AND t.vm_manager_code = :vm_manager_code");
		}
		
		sql.append(" AND t.vm_code NOT IN (SELECT sh_vip_code FROM t_sell_shop sh ");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code=sh.sh_shop_code AND shop.companyid=sh.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND sh.sh_state=0 AND sh.sh_vip_code IS NOT NULL AND sh.sh_vip_code != ''");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND sh.sh_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND sh.sh_date <= :enddate ");
		}
		sql.append(" AND sh.companyid = :companyid ");
		sql.append(" GROUP BY sh.sh_vip_code) ");
		
		sql.append(" AND t.companyid = :companyid ");
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND t.vm_shop_code = :vm_shop_code ");
		}
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_NoShopAnalysis> noshopvip_analysis_list(
			Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_manager_code = params.get("vm_manager_code");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" vm_id as id,vm_cardcode,vm_shop_code,sp_name,vm_mobile,vm_name,vm_mt_code,vm_sex,");
		sql.append(" vm_times,vm_total_money,vm_total_point,vm_points,vm_date,vm_manager_code,");
		sql.append(" (SELECT mt.mt_name FROM t_vip_membertype mt WHERE mt.mt_code=t.vm_mt_code AND mt.companyid=t.companyid limit 1) as mt_name,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em.em_code = t.vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager,");
		sql.append(" vm_lastbuy_date,vm_lastbuy_money ");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND t.vm_state=0 ");
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND sp.sp_code = :vm_shop_code ");
		}
		if( StringUtil.isNotEmpty(vm_mt_code)){
			sql.append(" AND t.vm_mt_code = :vm_mt_code");
		}
		if( StringUtil.isNotEmpty(vm_manager_code) ){
			sql.append(" AND t.vm_manager_code = :vm_manager_code");
		}
		
		sql.append(" AND t.vm_code NOT IN (SELECT sh_vip_code FROM t_sell_shop sh ");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code=sh.sh_shop_code AND shop.companyid=sh.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND sh.sh_state=0 AND sh.sh_vip_code IS NOT NULL AND sh.sh_vip_code != ''");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND sh.sh_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND sh.sh_date <= :enddate ");
		}
		sql.append(" AND sh.companyid = :companyid ");
		sql.append(" GROUP BY sh.sh_vip_code) ");
		
		sql.append(" AND t.companyid = :companyid ");
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND t.vm_shop_code = :vm_shop_code ");
		}
		
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY t.vm_id DESC ");
		}
		sql.append(" LIMIT :start,:end");
		
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
				new BeanPropertyRowMapper<>(T_Vip_NoShopAnalysis.class));
	}

	@Override
	public Map<String, Object> noshopvip_analysis_sum(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_manager_code = params.get("vm_manager_code");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" IFNULL(SUM(vm_times), 0) AS vm_times,");
		sql.append(" IFNULL(SUM(vm_total_money), 0) AS vm_total_money,");
		sql.append(" IFNULL(SUM(vm_total_point), 0) AS vm_total_point,");
		sql.append(" IFNULL(SUM(vm_points), 0) AS vm_points,");
		sql.append(" IFNULL(SUM(vm_lastbuy_money), 0) AS vm_lastbuy_money");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND t.vm_state=0 ");
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND sp.sp_code = :vm_shop_code ");
		}
		if( StringUtil.isNotEmpty(vm_mt_code)){
			sql.append(" AND t.vm_mt_code = :vm_mt_code");
		}
		if( StringUtil.isNotEmpty(vm_manager_code) ){
			sql.append(" AND t.vm_manager_code = :vm_manager_code");
		}
		
		sql.append(" AND t.vm_code NOT IN (SELECT sh_vip_code FROM t_sell_shop sh ");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code=sh.sh_shop_code AND shop.companyid=sh.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND sh.sh_state=0 AND sh.sh_vip_code IS NOT NULL AND sh.sh_vip_code != ''");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND sh.sh_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND sh.sh_date <= :enddate ");
		}
		sql.append(" AND sh.companyid = :companyid ");
		sql.append(" GROUP BY sh.sh_vip_code) ");
		
		sql.append(" AND t.companyid = :companyid ");
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND t.vm_shop_code = :vm_shop_code ");
		}
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public Map<String, Object> member_lose_data(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		/** 流失数 begin **/
		sql.append("(SELECT COUNT(vm_id) FROM t_vip_member t JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND sp.sp_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" AND t.vm_date < :loss_date ");//办卡日期
		sql.append(" AND vm_code NOT IN ");//查询出来购物会员，不在购物会员中的算流失会员
		sql.append("(SELECT sh_vip_code FROM t_sell_shop sh JOIN t_base_shop shop ");
		sql.append(" ON shop.sp_code=sh.sh_shop_code AND shop.companyid=sh.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND shop.sp_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND shop.sp_code = :shop_code");
		}
		sql.append(" AND sh.companyid = :companyid ");
		sql.append(" AND sh_vip_code IS NOT NULL AND sh_vip_code != ''  ");
		sql.append(" AND sh_date >=:loss_date ");
		sql.append(" AND sh_date <=:now_date ");
		sql.append("  GROUP BY sh_vip_code)) AS loss_vip,");
		/** 流失数 end **/
		
		/** 时间段内办卡数 begin **/
		sql.append(" (SELECT COUNT(1) FROM t_vip_member t JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND sp.sp_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		if(begindate != null && !"".equals(begindate)){
			sql.append(" AND t.vm_date >= :consumeDayDate ");
		}
		if(enddate != null && !"".equals(enddate)){
			sql.append(" AND t.vm_date <= :enddate ");
		}
		sql.append(" AND t.companyid = :companyid) AS new_vip, ");
		/** 时间段内办卡数 end **/
		
		
		/** 时间段内购买会员数 begin **/
		sql.append(" (SELECT COUNT( DISTINCT vm_code) FROM t_vip_member t JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND sp.sp_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND vm_code IN (SELECT DISTINCT sh_vip_code FROM t_sell_shop sh JOIN t_base_shop shop ");
		sql.append(" ON shop.sp_code=sh.sh_shop_code AND shop.companyid=sh.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND t.vm_shop_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND t.vm_shop_code = :shop_code");
		}
		sql.append(" AND sh.sh_vip_code IS NOT NULL AND sh.sh_vip_code != '' ");
		if( StringUtil.isNotEmpty(begindate) ){
			sql.append(" AND sh.sh_date >= :consumeDayDate ");
		}
		if( StringUtil.isNotEmpty(enddate) ){
			sql.append(" AND sh.sh_date <= :enddate ");
		}
		sql.append(" AND sh.companyid = :companyid) ");
		sql.append(" AND t.companyid = :companyid) AS buy_count_vip,");
		/** 时间段内购买会员数 end **/
		
		/** 周期内会员升级数量 begin**/
		/*sql.append(" ( SELECT COUNT(1) FROM (SELECT gd_id FROM t_vip_upgrade JOIN t_base_storeinfo ON si_code = VG_Si_Code AND si_ei_id = VG_Ei_Id WHERE 1=1 ");
		if(endDate != null && !"".equals(endDate)){
			sql.append(" AND DATE_FORMAT(VG_SysTime, '%Y-%m-%d') <= '"+endDate+"' ");
		}
		if(beginDate != null && !"".equals(beginDate)){
			sql.append(" AND DATE_FORMAT(VG_SysTime, '%Y-%m-%d') >= '"+beginDate+"' ");
		}
		if (Constants.SI_RT_TYPE_HQ.equals(rtCode) || Constants.SI_RT_TYPE_RG.equals(rtCode)){//总部和区域
			sql.append(" AND (si_upcode='"+logSICode+"' AND si_rt_code<>'"+Constants.SI_RT_TYPE_AS+"')");
			if(siCode!=null && !"".equals(siCode)){
				sql.append(" AND si_code = '"+siCode+"' ");
			}
		}
		if(Constants.SI_RT_TYPE_OS.equals(rtCode) || Constants.SI_RT_TYPE_AS.equals(rtCode)){//加盟店和自营店
			sql.append(" AND si_code = '"+logSICode+"' ");
		}
		sql.append(" AND VG_Ei_Id = '"+ei_id+"' GROUP BY VG_CardCode ) vg) AS goUpVip, ");*/
		/** 周期内会员升级数量 end**/
		
		/** 会员总数 begin **/
		sql.append(" (SELECT COUNT(1) FROM t_vip_member t JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND sp.sp_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND t.companyid = :companyid ) AS total_vip,");
		/** 会员总数 end **/
		/**周期新增会员的消费数量begin*/
		sql.append(" (select count(1) from ( SELECT vm_code FROM t_vip_member t JOIN t_sell_shop sh ON sh.sh_vip_code = t.vm_code AND sh.companyid = t.companyid  AND sh.sh_date >= :consumeDayDate");
		sql.append("  JOIN t_base_shop sp ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid  ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND sp.sp_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND t.vm_date <= :now_date ");
		sql.append(" AND t.vm_date >= :consumeDayDate ");

		sql.append(" AND t.companyid = :companyid  GROUP BY vm_code ) as vip_num) AS cyclevip_buy_count, ");
		/**周期新增会员的消费数量end*/
		/**会员保留人数begin*/
		sql.append(" (select count(DISTINCT sh_vip_code )  from (	SELECT t.sh_vip_code FROM t_sell_shop t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.sh_shop_code AND sp.companyid = t.companyid ");
		sql.append(" LEFT JOIN t_vip_member vm ON vm.vm_code = t.sh_vip_code AND vm.companyid=t.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND vm.vm_shop_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND vm.vm_shop_code = :shop_code");
		}
		sql.append(" AND vm.vm_state = 0 AND t.companyid = :companyid AND t.sh_vip_code IS NOT NULL AND t.sh_vip_code != '' ");
		sql.append(" AND t.sh_date >= :loss_date ");
		sql.append(" GROUP BY t.sh_vip_code) as  hold_count) as viphold_count");
		/**会员保留人数end*/
		sql.append("  FROM DUAL");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public Integer vipquota_analysis_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		String query_type = (String)params.get("query_type");
		
		StringBuffer sql = new StringBuffer();
		if("loss".equals(query_type)){//流失会员 
			sql.append(" SELECT COUNT(1) ");
			sql.append(" FROM t_vip_member t JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND sp.sp_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp.sp_code = :shop_code");
			}
			sql.append(" AND t.companyid = :companyid");
			sql.append(" AND vm_date < :loss_date ");//办卡日期
			sql.append(" AND vm_code NOT IN ");
			sql.append("(SELECT DISTINCT sh_vip_code FROM t_sell_shop sh JOIN t_base_shop shop ");
			sql.append(" ON shop.sp_code=sh.sh_shop_code AND shop.companyid=sh.companyid");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND shop.sp_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND shop.sp_code = :shop_code");
			}
			sql.append(" AND sh.companyid = :companyid ");
			sql.append(" AND sh_vip_code IS NOT NULL AND sh_vip_code != '' ");
			sql.append(" AND sh_date >=:loss_date ");
			sql.append(" AND sh_date <=:now_date )");
			
		}
		if("hold".equals(query_type)){//保留会员
			sql.append(" SELECT COUNT(1) FROM (");
			sql.append(" SELECT sh_vip_code from t_sell_shop t ");
			sql.append(" JOIN t_base_shop sp ON t.sh_shop_code=sp.sp_code AND t.companyid = sp.companyid ");
			sql.append(" LEFT JOIN t_vip_member vm ON vm.companyid =t.companyid AND vm.vm_code = t.sh_vip_code ");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND vm.vm_shop_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND vm.vm_shop_code = :shop_code");
			}
			sql.append(" AND t.companyid = :companyid AND vm.vm_state= 0 ");
			sql.append(" AND t.sh_vip_code IS NOT NULL AND t.sh_vip_code != '' ");
			sql.append(" AND t.sh_date >= :loss_date");//
			sql.append(" GROUP BY t.sh_vip_code) temp ");
		}
		if("goup".equals(query_type)){//转化率
			sql.append(" SELECT COUNT(1) FROM (");
			sql.append(" SELECT vm_code FROM t_vip_member t ");
		    sql.append(" JOIN t_sell_shop sh on t.companyid = sh.companyid AND t.vm_code=sh.sh_vip_code AND sh.sh_date >= :consumeDayDate ");
		    sql.append(" JOIN t_base_shop sp ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid  ");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND sp.sp_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp.sp_code = :shop_code");
			}
			sql.append(" AND t.vm_date >= :consumeDayDate ");
			sql.append(" AND t.companyid = :companyid ");
			sql.append(" GROUP BY vm_code ) temp ");
		}
		// TODO
		if("buyback".equals(query_type)){//购物会员
			sql.append(" SELECT COUNT(1) FROM (");
			sql.append(" SELECT sh_vip_code FROM t_vip_member t" );
		    sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid");
			sql.append(" JOIN t_sell_shop sh ON sh.sh_vip_code = t.vm_code AND sh.companyid = t.companyid  ");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND t.vm_shop_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND t.vm_shop_code = :shop_code");
			}
			sql.append(" AND LENGTH(sh.sh_vip_code) > 0");
			sql.append(" AND sh.sh_date <= :enddate ");
			sql.append(" AND sh.sh_date >= :consumeDayDate ");
			sql.append(" AND sh.companyid = :companyid" );
			sql.append(" GROUP BY sh.sh_vip_code )temp");
		}
		if("newvip".equals(query_type)){//新增会员
			sql.append(" SELECT COUNT(1) ");
			sql.append(" FROM t_vip_member t " );
		    sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND sp.sp_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp.sp_code = :shop_code");
			}
			if(enddate != null && !"".equals(enddate)){
				sql.append(" AND t.vm_date <= :enddate ");
			}
			if(begindate != null && !"".equals(begindate)){
				sql.append(" AND t.vm_date >= :consumeDayDate ");
			}
			sql.append(" AND t.companyid = :companyid ");
		}
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_QuotaAnalysis> vipquota_analysis_list(
			Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		String query_type = (String)params.get("query_type");
		
		StringBuffer sql = new StringBuffer();
		if("loss".equals(query_type)){//流失会员 
			sql.append("SELECT vm_id as id,vm_cardcode,vm_shop_code,sp_name,vm_name,vm_mt_code,vm_sex,");
			sql.append("vm_times,vm_total_money,vm_mobile,0 AS sh_times,0.00 AS sh_total_money,");
			sql.append("(SELECT mt.mt_name FROM t_vip_membertype mt WHERE mt.mt_code=t.vm_mt_code AND mt.companyid=t.companyid limit 1) as mt_name,");
			sql.append("(SELECT gd_name FROM t_vip_grade gd WHERE gd.companyid = t.companyid AND gd_lower <= sh_total_money AND gd_upper > sh_total_money LIMIT 0,1) AS gd_name");
			sql.append(" FROM t_vip_member t JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND sp.sp_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp.sp_code = :shop_code");
			}
			sql.append(" AND t.companyid = :companyid");
			sql.append(" AND vm_date < :loss_date ");//办卡日期
			sql.append(" AND vm_code NOT IN ");
			sql.append("(SELECT DISTINCT sh_vip_code FROM t_sell_shop sh JOIN t_base_shop shop ");
			sql.append(" ON shop.sp_code=sh.sh_shop_code AND shop.companyid=sh.companyid");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND shop.sp_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND shop.sp_code = :shop_code");
			}
			sql.append(" AND sh.companyid = :companyid ");
			sql.append(" AND sh_vip_code IS NOT NULL AND sh_vip_code != '' ");
			sql.append(" AND sh_date >=:loss_date ");
			sql.append(" AND sh_date <=:now_date )");
			if(sidx != null && !"".equals(sidx)){
				sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
			}else {
				sql.append(" ORDER BY t.vm_id DESC ");
			}
			sql.append(" LIMIT :start,:end");
		}
		if("hold".equals(query_type)){//保留会员
			sql.append(" SELECT cycle.sh_times,cycle.sh_total_money, vm_id as id,vm_shop_code,vm_cardcode,vm_mobile,vm_name,vm_sex,sp_name,vm_times,vm_total_money,");
			sql.append("(SELECT mt.mt_name FROM t_vip_membertype mt WHERE mt.mt_code=vm.vm_mt_code AND mt.companyid=vm.companyid limit 1) as mt_name,");
			sql.append("(SELECT gd_name FROM t_vip_grade gd WHERE gd.companyid = vm.companyid AND gd_lower <= sh_total_money AND gd_upper > sh_total_money LIMIT 0,1) AS gd_name");
			sql.append(" from t_sell_shop t ");
			sql.append(" JOIN t_base_shop sp ON t.sh_shop_code=sp.sp_code AND t.companyid = sp.companyid ");
			sql.append(" LEFT JOIN t_vip_member vm ON vm.companyid =t.companyid AND vm.vm_code = t.sh_vip_code ");
			sql.append(" LEFT JOIN (SELECT SUM(CASE WHEN sh_state = 0 THEN 1 ELSE CASE WHEN sh_state = 1 THEN -1 ELSE 0 END END) sh_times,IFNULL(SUM(sh_sell_money), 0) sh_total_money,sh.companyid,sh_vip_code from t_sell_shop sh WHERE sh.companyid = :companyid AND sh_date >= :loss_date AND sh_vip_code!='' GROUP BY sh_vip_code)");
			sql.append(" as cycle ON cycle.companyid=t.companyid AND cycle.sh_vip_code=t.sh_vip_code ");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND vm.vm_shop_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND vm.vm_shop_code = :shop_code");
			}
			sql.append(" AND t.companyid = :companyid AND vm.vm_state= 0 ");
			sql.append(" AND t.sh_vip_code IS NOT NULL AND t.sh_vip_code != '' ");
			sql.append(" AND t.sh_date >= :loss_date");//
			sql.append(" GROUP BY t.sh_vip_code ");
			if(sidx != null && !"".equals(sidx)){
				sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
			}else {
				sql.append(" ORDER BY vm.vm_id DESC ");
			}
			sql.append(" LIMIT :start,:end");
		}
		if("goup".equals(query_type)){//转化率
			sql.append(" SELECT vm_id as id,vm_cardcode,vm_shop_code,sp_name,");
			sql.append(" vm_name,vm_mt_code,vm_sex,vm_times,vm_total_money,vm_mobile,");
			sql.append(" IFNULL(sh_times, 0) AS sh_times,");
			sql.append(" IFNULL(sh_total_money, 0) AS sh_total_money,");
			sql.append("(SELECT mt.mt_name FROM t_vip_membertype mt WHERE mt.mt_code=t.vm_mt_code AND mt.companyid=t.companyid limit 1) as mt_name,");
			sql.append("(SELECT gd_name FROM t_vip_grade gd WHERE gd.companyid = t.companyid AND gd_lower <= sh_total_money AND gd_upper > sh_total_money LIMIT 0,1) AS gd_name");
			sql.append(" FROM t_vip_member t ");
		    sql.append(" JOIN t_sell_shop sh on t.companyid = sh.companyid AND t.vm_code=sh.sh_vip_code AND sh.sh_date >= :consumeDayDate ");
		    sql.append(" JOIN t_base_shop sp ON sp.sp_code=t.vm_shop_code AND sp.companyid=t.companyid  ");
			sql.append(" JOIN (SELECT sh_total_money,sh_times,sh_vip_code,companyid FROM");
			sql.append(" (SELECT IFNULL(SUM(sh_sell_money),0) AS sh_total_money,SUM(CASE WHEN sh_state = 0 THEN 1 ELSE CASE WHEN sh_state = 1 THEN -1 ELSE 0 END END)  AS sh_times,sh_vip_code,companyid");
			sql.append(" FROM t_sell_shop sell");
			sql.append(" WHERE LENGTH(sell.sh_vip_code)>0 ");
			sql.append(" AND sell.sh_date <= :enddate AND sh_date >= :consumeDayDate");
			sql.append(" AND sell.companyid = :companyid");
			sql.append(" GROUP BY sell.sh_vip_code) as gradetab");
			sql.append(" ) as temp ON temp.sh_vip_code = t.vm_code AND temp.companyid = t.companyid");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND sp.sp_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp.sp_code = :shop_code");
			}
			sql.append(" AND t.vm_date >= :consumeDayDate ");
			sql.append(" AND t.companyid = :companyid ");
			sql.append(" GROUP BY vm_code  ");
			if(sidx != null && !"".equals(sidx)){
				sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
			}else {
				sql.append(" ORDER BY id DESC ");
			}
			sql.append(" LIMIT :start,:end");
		}
		// TODO
		if("buyback".equals(query_type)){//购物会员
			sql.append("SELECT temp.*,");
			sql.append("(SELECT gd_name FROM t_vip_grade gd WHERE gd.companyid = temp.companyid AND gd_lower <= sh_total_money AND gd_upper > sh_total_money LIMIT 0,1) AS gd_name");
			sql.append(" FROM (");
			sql.append(" SELECT vm_id as id,vm_cardcode,vm_shop_code,sp_name,vm_name,vm_mt_code,vm_sex,");
			sql.append(" vm_times,vm_total_money,vm_mobile,t.companyid,");
			sql.append(" SUM(CASE WHEN sh_state = 0 THEN 1 ELSE CASE WHEN sh_state = 1 THEN -1 ELSE 0 END END)  AS sh_times,");
			sql.append(" SUM(sh_sell_money) AS sh_total_money,");
			sql.append("(SELECT mt.mt_name FROM t_vip_membertype mt WHERE mt.mt_code=t.vm_mt_code AND mt.companyid=t.companyid limit 1) AS mt_name ");
			sql.append(" FROM t_vip_member t" );
		    sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid");
			sql.append(" JOIN t_sell_shop sh ON sh.sh_vip_code = t.vm_code AND sh.companyid = t.companyid  ");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND t.vm_shop_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND t.vm_shop_code = :shop_code");
			}
			sql.append(" AND LENGTH(sh.sh_vip_code) > 0");
			sql.append(" AND sh.sh_date <= :enddate ");
			sql.append(" AND sh.sh_date >= :consumeDayDate ");
			sql.append(" AND sh.companyid = :companyid" );
			sql.append(" GROUP BY sh.sh_vip_code) temp ");
			if(sidx != null && !"".equals(sidx)){
				sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
			}else {
				sql.append(" ORDER BY id DESC ");
			}
			sql.append(" LIMIT :start,:end");
		}
		if("newvip".equals(query_type)){//新增会员
			sql.append(" SELECT vm_id as id,vm_cardcode,vm_shop_code,sp_name,vm_name,vm_mt_code,vm_sex,");
			sql.append(" vm_times,vm_total_money,vm_mobile,IFNULL(sh_times, 0) AS sh_times,IFNULL(sh_total_money, 0) AS sh_total_money,");
			sql.append(" (SELECT gd_name FROM t_vip_grade gd WHERE gd.companyid = t.companyid AND gd_lower <= IFNULL(sh_total_money, 0) AND gd_upper > IFNULL(sh_total_money, 0) LIMIT 0,1) AS gd_name,");
			sql.append("(SELECT mt.mt_name FROM t_vip_membertype mt WHERE mt.mt_code=t.vm_mt_code AND mt.companyid=t.companyid limit 1) AS mt_name ");
			sql.append(" FROM t_vip_member t " );
		    sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid");
			sql.append(" LEFT JOIN (");
			sql.append(" SELECT sh_total_money,sh_times,sh_vip_code FROM ");
			sql.append(" (SELECT IFNULL(SUM(sh_sell_money),0) AS sh_total_money,SUM(CASE WHEN sh_state = 0 THEN 1 ELSE CASE WHEN sh_state = 1 THEN -1 ELSE 0 END END ) AS sh_times,sh_vip_code,sell.companyid");
			sql.append(" FROM t_sell_shop sell ");
			sql.append(" WHERE sh_state = 0 ");
			sql.append(" AND LENGTH(sh_vip_code)>0");
			sql.append(" AND sh_date <= :enddate AND sh_date >= :begindate ");
			sql.append(" AND sell.companyid = :companyid");
			sql.append(" GROUP BY sh_vip_code) as gradetab");
			sql.append(" ) as temp on sh_vip_code = vm_code");
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
				if(vm_shop_code!=null && !"".equals(vm_shop_code)){
					sql.append(" AND sp.sp_code = :vm_shop_code ");
				}
			}else{//自营、加盟、合伙
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp.sp_code = :shop_code");
			}
			if(enddate != null && !"".equals(enddate)){
				sql.append(" AND t.vm_date <= :enddate ");
			}
			if(begindate != null && !"".equals(begindate)){
				sql.append(" AND t.vm_date >= :consumeDayDate ");
			}
			sql.append(" AND t.companyid = :companyid ");
			if(sidx != null && !"".equals(sidx)){
				sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
			}else {
				sql.append(" ORDER BY id DESC ");
			}
			sql.append(" LIMIT :start,:end");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
				new BeanPropertyRowMapper<>(T_Vip_QuotaAnalysis.class));
	}

	@Override
	public List<T_Vip_BrandAnalysis> brand_analysis_list(
			Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_cardcode = params.get("vm_cardcode");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT bd_id as id,bd_code,bd_name," );
		sql.append(" SUM(shl_amount) AS count_sum,");
		sql.append(" SUM(shl_money) AS money_sum,");
		sql.append(" SUM(shl_cost_price*shl_amount) AS cost_monery_sum,");
		sql.append(" SUM(shl_money-shl_cost_price*shl_amount) AS profits_sum");
		sql.append(" FROM t_sell_shoplist t ");
		sql.append(" JOIN t_base_product pd ON pd.pd_code=t.shl_pd_code AND pd.companyid=t.companyid ");
		sql.append(" JOIN t_base_brand bd ON bd.bd_code=pd.pd_bd_code AND bd.companyid=pd.companyid ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.shl_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND sp.sp_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" AND shl_state=0 ");
		sql.append(" AND LENGTH(shl_vip_code)>0 ");
		if(StringUtil.isNotEmpty(vm_cardcode)){
			sql.append(" AND INSTR(vm_cardcode, :vm_cardcode)>0");
		}
		sql.append("AND (DATE_FORMAT(shl_sysdate,'%Y-%m-%d')>=:begindate AND DATE_FORMAT(shl_sysdate,'%Y-%m-%d')<=:enddate) ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" GROUP BY bd_code ");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY id DESC ");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
				new BeanPropertyRowMapper<>(T_Vip_BrandAnalysis.class));
	}

	@Override
	public List<T_Vip_ConsumeMonthCompare> vipconsumeMonthCompareList(
			Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_year = params.get("vm_year");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT IFNULL(SUM(sh_money), 0) AS sh_money,DATE_FORMAT(sh_date, '%Y-%m') AS vm_month ");
		sql.append(" FROM t_sell_shop t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.sh_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(vm_shop_code!=null && !"".equals(vm_shop_code)){
				sql.append(" AND t.sh_shop_code = :vm_shop_code ");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND t.sh_shop_code = :shop_code");
		}
		if(vm_year!=null && !"".equals(vm_year)){
			sql.append(" AND INSTR(t.sh_date,:vm_year)>0 ");
		}
        sql.append(" AND t.sh_vip_code != ''");
        sql.append(" AND t.sh_vip_code IS NOT NULL ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" GROUP BY vm_month ");
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
				new BeanPropertyRowMapper<>(T_Vip_ConsumeMonthCompare.class));
	}

	@Override
	public List<T_Vip_AgeGroupSetUp> vipage_analysis_sell_column(
			Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_manager_code = params.get("vm_manager_code");
	        
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ags_id,ags_beg_age,ags_end_age,ags_shop_code,COUNT(vm_id) as vip_count,SUM(sh_sell_money) as sh_sell_money ");
		sql.append(" FROM t_vip_agegroupsetup t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.ags_shop_code AND sp.companyid = t.companyid");
		sql.append(" LEFT JOIN ");
		
		sql.append("(");
		sql.append(" SELECT vm_id,vm_shop_code,fn_GetVipAge(vm_birthday_type,vm_birthday,vm_lunar_birth) as vm_age,vm.companyid,");
		sql.append(" (SELECT SUM(sh_money) AS sh_sell_money FROM t_sell_shop sh WHERE sh.sh_vip_code = vm.vm_code AND sh.companyid = vm.companyid AND sh.sh_date>=:monthBegDate AND sh.sh_date<=:monthEndDate) AS sh_sell_money ");
		sql.append(" FROM t_vip_member vm ");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code = vm.vm_shop_code AND shop.companyid = vm.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND vm.vm_shop_code = :shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_mt_code)){
			sql.append(" AND vm.vm_mt_code = :vm_mt_code ");
		}
		if(StringUtil.isNotEmpty(vm_shop_code)){
			sql.append(" AND vm.vm_shop_code = :vm_shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_manager_code)){
			sql.append(" AND vm.vm_manager_code = :vm_manager_code ");
		}
		sql.append(" AND vm.vm_state = 0");
		sql.append(" AND vm.companyid = :companyid ");
		sql.append(") member ON member.companyid = t.companyid AND member.vm_age >= t.ags_beg_age AND member.vm_age <= t.ags_end_age ");
		
		sql.append(" WHERE 1=1 ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(" AND ags_shop_code = :shop_code");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" AND ags_shop_code = :shop_upcode");
		}
		sql.append(" AND t.companyid = :companyid "); 
		sql.append(" GROUP BY ags_id ");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_AgeGroupSetUp.class));
	}
	
	@Override
	public List<T_Vip_AgeGroupSetUp> vipage_analysis_column(
			Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_manager_code = params.get("vm_manager_code");
	        
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ags_id,ags_beg_age,ags_end_age,ags_shop_code,COUNT(vm_id) as vip_count ");
		sql.append(" FROM t_vip_agegroupsetup t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.ags_shop_code AND sp.companyid = t.companyid");
		sql.append(" LEFT JOIN ");
		
		sql.append("(");
		sql.append(" SELECT vm_id,vm_shop_code,fn_GetVipAge(vm_birthday_type,vm_birthday,vm_lunar_birth) as vm_age,vm.companyid FROM t_vip_member vm ");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code = vm.vm_shop_code AND shop.companyid = vm.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND vm.vm_shop_code = :shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_mt_code)){
			sql.append(" AND vm.vm_mt_code = :vm_mt_code ");
		}
		if(StringUtil.isNotEmpty(vm_shop_code)){
			sql.append(" AND vm.vm_shop_code = :vm_shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_manager_code)){
			sql.append(" AND vm.vm_manager_code = :vm_manager_code ");
		}
		sql.append(" AND vm.vm_state = 0");
		sql.append(") member ON member.companyid = t.companyid AND member.vm_age >= t.ags_beg_age AND member.vm_age <= t.ags_end_age ");
		
		sql.append(" WHERE 1=1 ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(" AND ags_shop_code = :shop_code");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" AND ags_shop_code = :shop_upcode");
		}
		sql.append(" AND t.companyid = :companyid "); 
		sql.append(" GROUP BY ags_id ");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_AgeGroupSetUp.class));
	}

	@Override
	public Integer vipage_analysis_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_manager_code = params.get("vm_manager_code");
	    
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1) FROM (");
		sql.append(" SELECT vm_id,vm_cardcode,vm_shop_code,");
		sql.append("fn_GetVipAge(vm_birthday_type,vm_birthday,vm_lunar_birth) as vm_age");
		sql.append(" FROM t_vip_member t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND t.vm_shop_code = :shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_mt_code)){
			sql.append(" AND t.vm_mt_code = :vm_mt_code ");
		}
		if(StringUtil.isNotEmpty(vm_shop_code)){
			sql.append(" AND t.vm_shop_code = :vm_shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_manager_code)){
			sql.append(" AND t.vm_manager_code = :vm_manager_code ");
		}
		sql.append(" AND t.vm_state = 0");
		sql.append(" AND t.companyid = :companyid");
		sql.append(") member ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND member.vm_age >= :ags_beg_age ");
		sql.append(" AND member.vm_age <= :ags_end_age ");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Vip_Member> vipage_analysis_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_manager_code = params.get("vm_manager_code");
	    
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT member.* FROM (");
		sql.append(" SELECT vm_id,vm_cardcode,vm_name,vm_mobile,vm_sex,vm_birthday_type,vm_birthday,vm_lunar_birth,vm_total_money,vm_shop_code,");
		sql.append("fn_GetVipAge(vm_birthday_type,vm_birthday,vm_lunar_birth) as vm_age,sp.sp_name as shop_name,");
		sql.append(" IFNULL((SELECT SUM(sh_money) AS sh_sell_money FROM t_sell_shop sh WHERE sh.sh_vip_code = t.vm_code AND sh.companyid = t.companyid AND sh.sh_date>=:monthBegDate AND sh.sh_date<=:monthEndDate),0) AS sh_sell_money,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager,");
		sql.append(" (SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name ");
		sql.append(" FROM t_vip_member t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND t.vm_shop_code = :shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_mt_code)){
			sql.append(" AND t.vm_mt_code = :vm_mt_code ");
		}
		if(StringUtil.isNotEmpty(vm_shop_code)){
			sql.append(" AND t.vm_shop_code = :vm_shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_manager_code)){
			sql.append(" AND t.vm_manager_code = :vm_manager_code ");
		}
		sql.append(" AND t.vm_state = 0");
		sql.append(" AND t.companyid = :companyid");
		sql.append(") member ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND member.vm_age >= :ags_beg_age ");
		sql.append(" AND member.vm_age <= :ags_end_age ");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY sh_sell_money DESC ");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
				new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}

	@Override
	public Map<String, Object> vipage_analysis_sum(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_manager_code = params.get("vm_manager_code");
	    
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" IFNULL(SUM(vm_total_money), 0) AS vm_total_money,");
		sql.append(" IFNULL(SUM(sh_sell_money), 0) AS sh_sell_money ");
		sql.append(" FROM (");
		sql.append(" SELECT vm_id,vm_cardcode,vm_shop_code,vm_total_money,");
		sql.append(" IFNULL((SELECT SUM(sh_money) AS sh_sell_money FROM t_sell_shop sh WHERE sh.sh_vip_code = t.vm_code AND sh.companyid = t.companyid AND sh.sh_date>=:monthBegDate AND sh.sh_date<=:monthEndDate),0) AS sh_sell_money,");
		sql.append("fn_GetVipAge(vm_birthday_type,vm_birthday,vm_lunar_birth) as vm_age");
		sql.append(" FROM t_vip_member t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type) || CommonUtil.FOUR.equals(shop_type)){//总公司、分公司、加盟店
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND t.vm_shop_code = :shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_mt_code)){
			sql.append(" AND t.vm_mt_code = :vm_mt_code ");
		}
		if(StringUtil.isNotEmpty(vm_shop_code)){
			sql.append(" AND t.vm_shop_code = :vm_shop_code ");
		}
		if(StringUtil.isNotEmpty(vm_manager_code)){
			sql.append(" AND t.vm_manager_code = :vm_manager_code ");
		}
		sql.append(" AND t.vm_state = 0");
		sql.append(" AND t.companyid = :companyid");
		sql.append(") member ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND member.vm_age >= :ags_beg_age ");
		sql.append(" AND member.vm_age <= :ags_end_age ");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
}
